﻿using BCCommon;
using BCCommon.TMS.RedPacket;
using BCEntity.TMS.RedPacket.RedPacket;
using BCEntity.TMS.RedPacket.RedPacketStatistics;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.TMS.RedPacket.RedPacket
{
    public class RedPacketData : IRedPacketData
    {
        private const string SQL_QUERY = @" SELECT
		                                        A.RedPacketId,
                                                A.RedPacketTypeId,
                                                A.RedPacketType,
		                                        A.RedPacketAmount,
		                                        A.GrundlagenType,
                                                A.RedPacketName,
		                                        A.RedPackCount,
		                                        A.RemainCount,
		                                        A.ReceivedCount,
		                                        A.AgentRecordId,
		                                        A.Enabled,
		                                        A.CreateAdminId,
		                                        A.CreateAdminName,
		                                        A.CreateTime,
		                                        A.UpdateTime,
		                                        A.EditAdminId,
		                                        A.EditAdminName
                                            FROM
                                                redpacket A ";
        private const string SQL_COUNT = @" SELECT IFNULL(SUM(A.RedPackCount),0) AS RedPackCount,
                                            IFNULL(SUM(A.RemainCount),0) AS RemainCount,
                                            IFNULL(SUM(A.ReceivedCount),0) AS ReceivedCount
                                                    FROM
                                                        redpacket A ";
        private readonly IDatabaseContext database;
        public RedPacketData(IDatabaseContext database)
        {
            this.database = database;
        }

        public async Task<RedPacketEntity> InsertAsync(RedPacketEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }

        public async Task<RedPacketEntity> UpdateAsync(RedPacketEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }

        public async Task<RedPacketEntity> GetSingle(long redPacketId)
        {
            var sql = typeof(RedPacketEntity).GetSelectSqlByPrimaryKeys();
            return await this.database.Slave.QueryFirstOrDefaultAsync<RedPacketEntity>(sql, new { redPacketId });
        }

        public async Task<IEnumerable<RedPacketEntity>> GetList(bool? isEnabled,long agentRecordId)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.AgentRecordId = ?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            if (isEnabled.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", isEnabled.Value, DbType.Boolean);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            string sql = $"{SQL_QUERY} {whereSql} GROUP BY A.RedPacketId ORDER BY A.CreateTime DESC ";
            return await this.database.Slave.QueryAsync<RedPacketEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<RedPacketEntity>, int)> Query(bool? isEnabled, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (isEnabled.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", isEnabled.Value, DbType.Boolean);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            string sqlCount = " SELECT COUNT(A.RedPacketId) FROM redpacket A ";
            string sql = $@"{SQL_QUERY} {whereSql} GROUP BY A.RedPacketId ORDER BY A.CreateTime DESC LIMIT {(pageIndex - 1) * pageSize}, {pageSize};
                                {sqlCount}{whereSql} ";
            return await this.database.Slave.QueryPageableListAsync<RedPacketEntity>(sql, parameters);
        }

        public async Task<RedPacketCountEntity> GetRedPacketCount(long agentRecordId)
        {
            var sqlWhere = " WHERE A.AgentRecordId=?AgentRecordId ";
            var sql = $" {SQL_COUNT} {sqlWhere} ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<RedPacketCountEntity>(sql, new { agentRecordId });
        }

        public async Task<RedPacketEntity> GetRedPacket(decimal redPacketAmount, long agentRecordId)
        {
            var sql = $" {SQL_QUERY} WHERE RedPacketAmount=?RedPacketAmount AND AgentRecordId=?AgentRecordId";
            return await this.database.Slave.QueryFirstOrDefaultAsync<RedPacketEntity>(sql, new { redPacketAmount, agentRecordId });
        }

        public async Task<IEnumerable<RedPacketEntity>> GetRemainList(bool? isEnabled, long agentRecordId,
            GrundlagenTypeEnum grundlagenType)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.RemainCount > 0 ");
            wheres.Add("A.AgentRecordId = ?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            wheres.Add("A.GrundlagenType = ?GrundlagenType");
            parameters.Add("?GrundlagenType", grundlagenType.ToString(), DbType.AnsiString);
            if (isEnabled.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", isEnabled.Value, DbType.Boolean);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            string sql = $"{SQL_QUERY} {whereSql} GROUP BY A.RedPacketId ORDER BY A.CreateTime DESC ";
            return await this.database.Slave.QueryAsync<RedPacketEntity>(sql, parameters);
        }

        public async Task<RedPacketEntity> Receive(long redPacketId)
        {
            var sql = $@" UPDATE redpacket SET RemainCount = RemainCount - 1, ReceivedCount = ReceivedCount + 1, UpdateTime = NOW() WHERE RedPacketId = ?RedPacketId;
                                {SQL_QUERY} WHERE A.RedPacketId=?RedPacketId ";
            return await this.database.Master.QueryFirstOrDefaultAsync<RedPacketEntity>(sql, new { redPacketId }, database.Transaction);
        }

        public async Task<RedPacketAgentStatisticsEntity> GetRedPacketAgentStatistics(long agentRecordId,
            GrundlagenTypeEnum? grundlagenType)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.AgentRecordId = ?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            if (grundlagenType.HasValue)
            {
                wheres.Add("A.GrundlagenType = ?GrundlagenType");
                parameters.Add("?GrundlagenType", grundlagenType.ToString(), DbType.AnsiString);
            }
            var sqlQuery = $@" SELECT SUM(A.RedPackCount) AS AllCount,
                            SUM(A.ReceivedCount) AS ReceiveCount,
                            SUM(A.RemainCount) AS RemainCount,
                            SUM(A.RedPackCount)*A.RedPacketAmount AS AllAmount,
                            SUM(A.ReceivedCount)*A.RedPacketAmount AS ReceiveAmount,
                            SUM(A.RemainCount)*A.RedPacketAmount AS RemainAmount
                             FROM redpacket A ";
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            var sql = $" {sqlQuery} {whereSql} ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<RedPacketAgentStatisticsEntity>(sql, parameters);
        }
    }
}
